from flask import render_template, request, flash, redirect, url_for, abort
from sqlalchemy import or_, text

from collections import defaultdict
from datetime import datetime

from extensions import db
from models import HouseInfoFinal
from app_helpers import (
    get_per_page,
    paginate_list,
    price_in_range,
    related_houses,
    current_user,
)

from . import main_bp


@main_bp.route('/')
def index():
    total_houses = HouseInfoFinal.get_total_count()
    recommended_houses = HouseInfoFinal.get_recommended_houses()
    hot_houses = HouseInfoFinal.get_hot_houses()
    return render_template(
        'index.html',
        total_houses=total_houses,
        recommended_houses=recommended_houses,
        hot_houses=hot_houses,
    )


@main_bp.route('/list/<string:category>/<int:page>')
def list_category(category: str, page: int = 1):
    per_page = get_per_page(9)
    sort_option = request.args.get('sort', 'latest')
    fallback_notice = None

    def apply_sorting(queryset, selected):
        if selected == 'hot':
            return queryset.order_by(
                db.func.coalesce(HouseInfoFinal.page_views, 0).desc(),
                HouseInfoFinal.publish_time.desc()
            )
        return queryset.order_by(HouseInfoFinal.publish_time.desc())

    if category == 'pattern':
        title = '更多北京房源推荐'
        sort_option = sort_option if sort_option == 'hot' else 'latest'
        like_pattern = '%北京%'
        query = HouseInfoFinal.query.filter(
            or_(
                HouseInfoFinal.region.like(like_pattern),
                HouseInfoFinal.block.like(like_pattern),
                HouseInfoFinal.address.like(like_pattern),
                HouseInfoFinal.title.like(like_pattern),
            )
        )
        query = apply_sorting(query, sort_option)
    elif category == 'hot_house':
        title = '更多热点房源推荐'
        sort_option = 'hot'
        query = apply_sorting(HouseInfoFinal.query, sort_option)
    else:
        abort(404)

    pagination = query.paginate(page=page, per_page=per_page, error_out=False)

    if category == 'pattern' and pagination.total == 0:
        fallback_notice = '数据库中暂未检索到包含“北京”的房源，为您展示最新发布的房源。'
        fallback_query = apply_sorting(HouseInfoFinal.query, sort_option)
        pagination = fallback_query.paginate(page=page, per_page=per_page, error_out=False)

    return render_template(
        'search_list.html',
        houses=pagination,
        title=title,
        category=category,
        fallback_notice=fallback_notice,
        sort_option=sort_option,
    )


@main_bp.route('/query')
def query_houses():
    page = request.args.get('page', 1, type=int)
    addr = (request.args.get('addr') or '').strip()
    rooms = (request.args.get('rooms') or '').strip()
    min_price = request.args.get('min_price', type=float)
    max_price = request.args.get('max_price', type=float)

    if addr:
        keyword = addr
        keyword_param = 'addr'
    elif rooms:
        keyword = rooms
        keyword_param = 'rooms'
    else:
        flash('请输入搜索关键字', 'warning')
        return redirect(url_for('main.index'))

    per_page = get_per_page(9)
    query = HouseInfoFinal.query

    if keyword_param == 'addr':
        like_pattern = f'%{keyword}%'
        query = query.filter(
            or_(
                HouseInfoFinal.region.like(like_pattern),
                HouseInfoFinal.block.like(like_pattern),
                HouseInfoFinal.address.like(like_pattern),
            )
        )
    else:
        query = query.filter(HouseInfoFinal.rooms.like(f'%{keyword}%'))

    houses = query.order_by(HouseInfoFinal.publish_time.desc()).all()
    filtered = [
        house for house in houses
        if price_in_range(house, min_price, max_price)
    ]
    pagination = paginate_list(filtered, page, per_page)

    return render_template(
        'list.html',
        houses=pagination,
        keyword=keyword,
        keyword_param=keyword_param,
        min_price=min_price,
        max_price=max_price,
    )


@main_bp.route('/house/<int:house_id>')
def house_detail(house_id: int):
    house = HouseInfoFinal.query.get_or_404(house_id)
    user = current_user()
    is_collected = user.is_collected(house_id) if user else False

    house.page_views = (house.page_views or 0) + 1
    if user:
        user.add_to_seen(house_id)

    try:
        db.session.commit()
    except Exception:
        db.session.rollback()

    recommended_houses = related_houses(house)

    region_query = HouseInfoFinal.query.filter(
        HouseInfoFinal.region == house.region
    )
    region_houses = region_query.limit(300).all()

    scatter_points = [
        [h.area_value, h.price_value]
        for h in region_houses
        if h.area_value and h.price_value
    ]

    room_counts = defaultdict(int)
    for h in region_houses:
        room_counts[h.rooms or '未知'] += 1
    pie_data = [
        {'name': name, 'value': count}
        for name, count in sorted(room_counts.items(), key=lambda x: x[1], reverse=True)[:5]
    ]

    community_stats = db.session.query(
        HouseInfoFinal.address,
        db.func.count(HouseInfoFinal.id).label('count')
    ).filter(
        HouseInfoFinal.region == house.region,
        HouseInfoFinal.address.isnot(None),
        HouseInfoFinal.address != ''
    ).group_by(HouseInfoFinal.address).order_by(db.func.count(HouseInfoFinal.id).desc()).limit(8).all()

    column_data = {
        'names': [stat.address for stat in community_stats],
        'values': [stat.count for stat in community_stats]
    }

    monthly_prices = defaultdict(list)
    for h in region_houses:
        if not h.publish_time or not h.price_value:
            continue
        month = datetime.fromtimestamp(h.publish_time).strftime('%Y-%m')
        monthly_prices[month].append(h.price_value)

    trend_dates = sorted(monthly_prices.keys())[-8:]
    trend_values = [
        round(sum(monthly_prices[m]) / len(monthly_prices[m]), 2)
        for m in trend_dates
    ]

    chart_data = {
        'scatter': scatter_points,
        'pie': pie_data,
        'column': column_data,
        'trend': {
            'dates': trend_dates,
            'values': trend_values
        }
    }

    return render_template(
        'detail_page.html',
        house=house,
        recommended_houses=recommended_houses,
        is_collected=is_collected,
        chart_data=chart_data,
    )


@main_bp.route('/health')
def health_check():
    try:
        db.session.execute(text('SELECT 1'))
        return {'status': 'healthy', 'database': 'connected', 'message': '应用运行正常'}
    except Exception as exc:
        return {'status': 'unhealthy', 'database': 'disconnected', 'error': str(exc)}, 500
